﻿using IgrMensageiros.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MODELO;
using log4net;
using System.Data.SqlClient;
using System.Data;
using CONEXAO;

namespace DAO
{
    public class PermissaoDAL : IUsuario
    {
        private static log4net.ILog log;


        public PermissaoDAL()
        {
            log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
            log4net.Config.XmlConfigurator.Configure();   
        }

        public void Gravar(Usuario pUsuario)
        {
           SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
           StringBuilder sb = new StringBuilder();
           String vSQL = ""; 

            using (SqlConnection cn = ConexaoBanco.GetConexao())
            {
                try
                {
                    sb.AppendLine("INSERT INTO TB_USUARIO_PERMISSAO (EMAIL, LOGIN, NOME, PERFIL, SENHA)");
                    sb.AppendLine(" VALUES ");
                    sb.AppendLine("(@pEmail, @pLogin, @pNome, @pPerfil, @pSenha)");

                    vSQL = sb.ToString();

                    SqlCommand cmd = new SqlCommand(vSQL, cn);
                    cmd.Parameters.Clear();

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@pEmail", SqlDbType.Text ).Value = pUsuario.Email;
                    cmd.Parameters.Add("@pLogin", SqlDbType.Text ).Value = pUsuario.Login;
                    cmd.Parameters.Add("@pNome", SqlDbType.Text ).Value = pUsuario.Nome ;
                    cmd.Parameters.Add("@pPerfil", SqlDbType.Char).Value = pUsuario.Perfil ;
                    cmd.Parameters.Add("@pSenha", SqlDbType.Text ).Value = pUsuario.Senha ;

                    cn.Open();
                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    log.Error(ex);
                    throw;
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public void Deletar(int pID)
        {

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

            using (SqlConnection cn = ConexaoBanco.GetConexao())
            {
                try
                {
                    String Sql = "DELETE FROM TB_USUARIO_PERMISSAO WHERE ID = @ID)";
                    SqlCommand cmd = new SqlCommand(Sql, cn);
                    cmd.Parameters.Clear();

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = pID;
                    cn.Open();
                    cmd.ExecuteNonQuery();
                    cn.Close();
                }
                catch (Exception ex)
                {
                    log.Error(ex);
                    throw;
                }
                finally
                {
                    cn.Close();
                }
            }
        }
        
        public void Alterar(Usuario pUsuario)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
             StringBuilder sb = new StringBuilder();
            String vSQL = ""; 

            using (SqlConnection cn = ConexaoBanco.GetConexao())
            {
                try
                {
                    sb.AppendLine("UPDATE TB_USUARIO_PERMISSAO SET (EMAIL = @pEmail, LOGIN = @pLogin, NOME = @pNome, PERFIL = @pPerfil, SENHA = @pSenha");
                    sb.AppendLine("WHERE ID = @ID)");

                    vSQL = sb.ToString();

                    SqlCommand cmd = new SqlCommand(vSQL, cn);
                    cmd.Parameters.Clear();

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@pID", SqlDbType.Int).Value = pUsuario.ID;
                    cmd.Parameters.Add("@pEmail", SqlDbType.Text).Value = pUsuario.Email;
                    cmd.Parameters.Add("@pLogin", SqlDbType.Text).Value = pUsuario.Login;
                    cmd.Parameters.Add("@pNome", SqlDbType.Text).Value = pUsuario.Nome;
                    cmd.Parameters.Add("@pPerfil", SqlDbType.Char).Value = pUsuario.Perfil;
                    cmd.Parameters.Add("@pSenha", SqlDbType.Text).Value = pUsuario.Senha;
                    

                    cn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    log.Error(ex);
                    throw;
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public DataSet Listar(Usuario pUsuario)
        {
             SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            DataSet lstDataSet = new DataSet();

            using (SqlConnection cn = ConexaoBanco.GetConexao())
            {
                try
                {
                    String Sql = "SELECT * FROM TB_USUARIO_PERMISSAO WHERE ID = @pUSUARIO";
                    SqlCommand cmd = new SqlCommand(Sql, cn);
                    cmd.Parameters.Clear();

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@pUSUARIO", SqlDbType.Text).Value = pUsuario.ID;
                    cn.Open();
                    sqlDataAdapter.SelectCommand = cmd;
                    sqlDataAdapter.Fill(lstDataSet);

                    return lstDataSet;
                }
                catch (SqlException ex)
                {
                    log.Error(ex);
                    throw new Exception("ERRO:" + ex.Message);
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public DataSet GetUsuario(Usuario pUsuario)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            DataSet lstDataSet = new DataSet();

            using (SqlConnection cn = ConexaoBanco.GetConexao())
            {
                try
                {
                    String Sql = "SELECT * FROM TB_USUARIO_PERMISSAO WHERE LOGIN = @pLOGIN";
                    SqlCommand cmd = new SqlCommand(Sql, cn);
                    cmd.Parameters.Clear();

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@pLOGIN", SqlDbType.VarChar).Value = pUsuario.Login;
                    cn.Open();
                    sqlDataAdapter.SelectCommand = cmd;
                    sqlDataAdapter.Fill(lstDataSet);

                    return lstDataSet;
                }
                catch (SqlException ex)
                {
                    log.Error(ex);
                    throw new Exception("ERRO:" + ex.Message);
                }
                finally
                {
                    cn.Close();
                }
            }
        }
    }
}
